In SQL Server 2018 I have three tables:
T1 (idService, dateStart, dateStop)
T2 (idService, totalCostOfService)
T3 (idService, companyName)
Using joins, I created a view:
V1 (idService, dateStart, dateStop, totalCostOfService, companyName)
And we are fine. I can do my selects on the view and obtain the list of services done.
What I would like to do now is to duplicate every row of the view n times, where n=dateStart-dateStop; every row should have a "new" totalCostOfService = totalCostOfService/n.
I can do that using a temporary table, declaring variables, insert in temp using some while etc. etc. Let's call it "the procedure"
But what I would like to understand is:
is it possibile to do that directly with a select on V1? If not, is it possible to save "the procedure" as a view so that I can have it as a easy select?
Sorry if my question looks somewhat stupid, but I'm totally new with SQL. I tried searching here and on google but I couldn't find what an answer to my questions.
Thank you!
Thom A :
Rather than an rCTE (which is RBAR), you could use a Tally Table:\n\nWITH N AS (\n SELECT N\n FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),\nTally AS(\n SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I\n FROM N N1\n CROSS JOIN N N2 --100\n CROSS JOIN N N3 --1000\n CROSS JOIN N N4) --10000\nSELECT *\nFROM YourTable\n JOIN Tally T ON T.I <= dateStart-dateStop --Assumes dateStart and DateStop are integer values, even though their name implies otherwise\n --If they are dates, then use DATEDIFF(DAY, dateStart, dateEnd)\n\n\nThat tally will generate numbers up to 10000 (which over 27 years worth of days. That should be far more than enough).",
2019-03-14T10:44:29
George Menoutis :
I will assume the existence of a numbers table which has the column val for the individual value numbers. If you don't, you will find plenty by searching around.\n\nAdd this in the end of the FROM clause of your view:\n\ncross apply (select datediff(day,T1.dateStart,T1.dateStop)+1 as n_days)q1 -- number of days INCLUDING start\ncross apply (select dateadd(day,T1.dateStart,n.val) as day_of_charge)q2 from numbers n where n.val between 0 and n_days-1)\n\n\nThen you will be able to have the following field on your SELECT:\n\nT2.totalCostOfService/n_days as totalCostOfService\n\nI'll add a numbers table solution shortly.",
2019-03-14T10:28:29